/**
 * 
 */
package com.shine.icis.dao.imp;

import java.util.List;

import org.hibernate.Query;

import com.shine.cotrun.sql.QueryParameters;
import com.shine.cotrun.sql.hibernate.CommQuery;
import com.shine.cotrun.web.comm.ListPage;
import com.shine.icis.dao.IDataCheckDAO;
import com.shine.icis.dao.imp.BaseHibernateDAO;
import com.shine.icis.dto.DataCheck;
import com.shine.icis.hbean.DataCheckDef;
import com.shine.icis.hbean.DataCheckR08;
import com.shine.icis.hbean.DataCheckR09;
import com.shine.icis.hbean.DataCheckR10;
import com.shine.icis.hbean.DataCheckR11;
import com.shine.icis.hbean.DataCheckR12;
import com.shine.icis.hbean.DataCheckR13;
import com.shine.icis.hbean.ETLContextVar;
import com.shine.icis.hbean.R12ChkItem;
import com.shine.icis.hbean.R13ChkFactAmt;
import com.shine.icis.hbean.SettleAmtDetail;

/**
 * @author zsy
 *
 */
public class DataCheckDAO extends BaseHibernateDAO implements IDataCheckDAO {
	
	@SuppressWarnings("unchecked")
	@Override
	public List<DataCheckDef> findDataCheckDefs(char enableFlag) {
		String sql = null;
		if (enableFlag == DataCheckDef.ENABLE) {
			sql = "from DataCheckDef as d where d.enableFlag<>:enableFlag order by d.id";
			enableFlag = DataCheckDef.DISABLE;
		} else {
			sql = "from DataCheckDef as d where d.enableFlag=:enableFlag order by d.id";
		}
		Query query = getSession().createQuery(sql);
		query.setCharacter("enableFlag", enableFlag);
		return query.list();
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public ListPage<DataCheckR08> findDataCheckR08Page(QueryParameters qp) {
		if (qp == null) {
			throw new IllegalArgumentException("非法参数:查询参数对象为null");
		}
		try {
			StringBuffer sbQueryString = new StringBuffer();			
			sbQueryString.append("select c from DataCheckR08 as c where 1=1");
			if(qp.getParameter("tradeDate") != null){
				sbQueryString.append(" and c.tradeDate=:tradeDate");
			}
			if(qp.getParameter("sjrq") != null){
				sbQueryString.append(" and c.sjrq=:sjrq");
			}
			if(qp.getParameter("curTypeSipf") != null){
				sbQueryString.append(" and c.curTypeSipf=:curTypeSipf");
			}
			if(qp.getParameter("fundAcct") != null){
				sbQueryString.append(" and c.fundAcct=:fundAcct");
			}
			if(qp.getParameter("passed") != null){
				boolean passed = (Boolean)qp.getParameter("passed");
				sbQueryString.append(" and c.discAmt " + (passed ? "=" : "<>") + " 0");
			}
			
			return new CommQuery<DataCheckR08>().queryListPage(qp, 
					qp.appendOrders(sbQueryString, "c" ), getSession());
		} catch(RuntimeException re) {
			re.printStackTrace();
			return new  ListPage<DataCheckR08>();
		}
	}
	
	/**
	 * 资金账户完整性核对
	 * @author zhangxr 2010-11-16
	 * @param qp
	 **/
	@Override
	public ListPage<DataCheckR09> findDataCheckR09Page(QueryParameters qp) {
		if (qp == null) {
			throw new IllegalArgumentException("非法参数:查询参数对象为null");
		}
		try {
			StringBuffer sbQueryString = new StringBuffer();			
			sbQueryString.append("select c from DataCheckR09 as c where 1=1");
			if(qp.getParameter("sjrq") != null){
				sbQueryString.append(" and c.sjrq=:sjrq");
			}
			if(qp.getParameter("fundAcct") != null){
				sbQueryString.append(" and c.fundAcct=:fundAcct");
			}
			if(qp.getParameter("compareFlag") != null){
				sbQueryString.append(" and c.compareFlag=:compareFlag");
			}
			
			return new CommQuery<DataCheckR09>().queryListPage(qp, 
					qp.appendOrders(sbQueryString, "c" ), getSession());
		} catch(RuntimeException re) {
			re.printStackTrace();
			return new  ListPage<DataCheckR09>();
		}
	}
	
	/**
	 * 账户一致性核对
	 * @author zhangxr 2010-10-27
	 * @param qp
	 **/
	@Override
	public ListPage<DataCheckR10> findDataCheckR10Page(QueryParameters qp) {
		if (qp == null) {
			throw new IllegalArgumentException("非法参数:查询参数对象为null");
		}
		try {
			StringBuffer sbQueryString = new StringBuffer();			
			sbQueryString.append("select c from DataCheckR10 as c where 1=1");
			if(qp.getParameter("fundAcct") != null){
				sbQueryString.append(" and c.fundAcct=:fundAcct");
			}
			if(qp.getParameter("sjrq") != null){
				sbQueryString.append(" and c.sjrq=:sjrq");
			}
			if(qp.getParameter("compareFlag") != null){
				sbQueryString.append(" and c.compareFlag=:compareFlag");
			}
			
			return new CommQuery<DataCheckR10>().queryListPage(qp, 
					qp.appendOrders(sbQueryString, "c" ), getSession());
		} catch(RuntimeException re) {
			re.printStackTrace();
			return new  ListPage<DataCheckR10>();
		}
	}	
	
	/**
	 * 客户资金结算勾稽核对
	 * @author zhangxr 2010-10-27
	 * @param qp
	 **/
	@Override
	public ListPage<DataCheckR11> findDataCheckR11Page(QueryParameters qp) {
		if (qp == null) {
			throw new IllegalArgumentException("非法参数:查询参数对象为null");
		}
		try {
			StringBuffer sbQueryString = new StringBuffer();			
			sbQueryString.append("select c from DataCheckR11 as c where 1=1");
			if(qp.getParameter("tradeDate") != null){
				sbQueryString.append(" and c.tradeDate=:tradeDate");
			}
			if(qp.getParameter("sjrq") != null){
				sbQueryString.append(" and c.sjrq=:sjrq");
			}
			if(qp.getParameter("fundAcct") != null){
				sbQueryString.append(" and c.fundAcct=:fundAcct");
			}
			if(qp.getParameter("passed") != null){
				boolean passed = (Boolean)qp.getParameter("passed");
				sbQueryString.append(" and c.discAmt " + (passed ? "=" : "<>") + " 0");
			}
			
			return new CommQuery<DataCheckR11>().queryListPage(qp, 
					qp.appendOrders(sbQueryString, "c" ), getSession());
		} catch(RuntimeException re) {
			re.printStackTrace();
			return new  ListPage<DataCheckR11>();
		}
	}	
	
	@Override
	public ListPage<DataCheckR12> findDataCheckR12Page(QueryParameters qp) {
		if (qp == null) {
			throw new IllegalArgumentException("非法参数:查询参数对象为null");
		}
		try {
			StringBuffer sbQueryString = new StringBuffer();			
			sbQueryString.append("select c from DataCheckR12 as c where c.relateObj is null");
			if(qp.getParameter("busiDate") != null){
				sbQueryString.append(" and c.busiDate=:busiDate");
			}
			
			return new CommQuery<DataCheckR12>().queryListPage(qp, 
					qp.appendOrders(sbQueryString, "c" ), getSession());
		} catch(RuntimeException re) {
			re.printStackTrace();
			return new  ListPage<DataCheckR12>();
		}
	}
	
	@Override
	public DataCheckR12 findDataCheckR12(int busiDate) {
		Query query = this.getSession().createQuery("from DataCheckR12 as dc where dc.busiDate=:busiDate and dc.relateObj is null order by dc.id desc");
		query.setInteger("busiDate", busiDate);
		query.setMaxResults(1);
		return (DataCheckR12)query.uniqueResult();
	}
	
	@Override
	public DataCheckR12 findDataCheckR12ById(int id) {
		DataCheckR12 r12 = (DataCheckR12)getSession().get(DataCheckR12.class, id);
		return r12;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<R12ChkItem> findChkItems(char enableFlag) {
		Query query = this.getSession().createQuery("from R12ChkItem as ri where ri.enableFlag=:enableFlag order by ri.itemId");
		query.setCharacter("enableFlag", enableFlag);
		return query.list();
	}
	
	/**
	 * 查询未通过的记录总数
	 * @author zhangxr 2010-10-27
	 * */
	public DataCheck findNotPassed(int sjrq){
		DataCheck dc = new DataCheck();
		try {
			Query query = this.getSession().createQuery("select count(c.id) as num from DataCheckR08 as c where c.discAmt!=0 and c.sjrq=:sjrq");
			query.setParameter("sjrq",sjrq);
			Long notPasswdR08 = (Long)query.uniqueResult();
			dc.setNotPassedR08(Integer.parseInt(String.valueOf(notPasswdR08)));

			query = this.getSession().createQuery("select count(c.id) as num from DataCheckR09 as c where c.compareFlag=1 and c.sjrq=:sjrq ");
			query.setParameter("sjrq",sjrq);
			Long notPasswdR09 = (Long)query.uniqueResult();
			dc.setNotPassedR09(Integer.parseInt(String.valueOf(notPasswdR09)));

			query = this.getSession().createQuery("select count(c.id) as num from DataCheckR10 as c where c.sjrq=:sjrq");
			query.setParameter("sjrq",sjrq);
			Long notPasswdR10 = (Long)query.uniqueResult();
			dc.setNotPassedR10(Integer.parseInt(String.valueOf(notPasswdR10)));

			query = this.getSession().createQuery("select count(c.id) as num from DataCheckR11 as c where c.discAmt!=0 and c.sjrq=:sjrq");
			query.setParameter("sjrq",sjrq);
			Long notPasswdR11 = (Long)query.uniqueResult();
			dc.setNotPassedR11(Integer.parseInt(String.valueOf(notPasswdR11)));
			
			//只能判断最近一条的信息，万一哪次跑时核对信息没插入成功，显示结果就会错位
			query = this.getSession().createQuery("select c.discAmt as num from DataCheckR12 as c where c.busiDate=:sjrq and c.relateObj is null order by c.id desc");
			query.setParameter("sjrq",sjrq);
			query.setMaxResults(1);
			Double notPasswdR12 = (Double)query.uniqueResult();
			if (notPasswdR12 != null && notPasswdR12.doubleValue() != 0) {
				dc.setNotPassedR12(1);
			}
			query = this.getSession().createQuery("select c.discAmt as num from DataCheckR13 as c where c.busiDate=:sjrq and c.relateObj is null order by c.id desc");
			query.setParameter("sjrq",sjrq);
			query.setMaxResults(1);
			Double notPasswdR13 = (Double)query.uniqueResult();
			if (notPasswdR13 != null && notPasswdR13.doubleValue() != 0) {
				dc.setNotPassedR13(1);
			}
			
			return dc;
		} catch(RuntimeException re) {
			re.printStackTrace();
			return dc;
		}
	}
	@Override
	public ListPage<DataCheckR13> findDataCheckR13Page(QueryParameters qp) {
		if (qp == null) {
			throw new IllegalArgumentException("非法参数:查询参数对象为null");
		}
		try {
			StringBuffer sbQueryString = new StringBuffer();			
			sbQueryString.append("select c from DataCheckR13 as c where c.relateObj is null");
			if(qp.getParameter("busiDate") != null){
				sbQueryString.append(" and c.busiDate=:busiDate");
			}
			
			return new CommQuery<DataCheckR13>().queryListPage(qp, 
					qp.appendOrders(sbQueryString, "c" ), getSession());
		} catch(RuntimeException re) {
			re.printStackTrace();
			return new  ListPage<DataCheckR13>();
		}
	}
	
	@Override
	public DataCheckR13 findDataCheckR13(int busiDate) {
		Query query = this.getSession().createQuery("from DataCheckR13 as dc where dc.busiDate=:busiDate and dc.relateObj is null order by dc.id desc");
		query.setInteger("busiDate", busiDate);
		query.setMaxResults(1);
		return (DataCheckR13)query.uniqueResult();
	}
	
	@Override
	public DataCheckR13 findDataCheckR13ById(int id) {
		DataCheckR13 r13 = (DataCheckR13)getSession().get(DataCheckR13.class, id);
		return r13;
	}
	@Override
	public R13ChkFactAmt findChkFactAmt(int busiDate) {
		R13ChkFactAmt factAmt = (R13ChkFactAmt)getSession().get(R13ChkFactAmt.class, busiDate);
		return factAmt;
	}
	@Override
	public SettleAmtDetail findSettleAmtDetail(String dateType,int queryDate) {
		Query query;
		if ("0".equals(dateType)) {
			query = this.getSession().createQuery("from SettleAmtDetail as dc where dc.clearDate=:busiDate ");
		} else {
			query = this.getSession().createQuery("from SettleAmtDetail as dc where dc.settleDate=:busiDate ");
		}
		
		query.setInteger("busiDate", queryDate);
		query.setMaxResults(1);
		return (SettleAmtDetail)query.uniqueResult();
		//SettleAmtDetail sad = (SettleAmtDetail)getSession().get(SettleAmtDetail.class, busiDate);
		//return sad;
	}
}
